﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
using Entity;

namespace DataAccess.Service
{
    public class PaymentProjectService:IPaymentProjectService
    {
        /// <summary>
        /// 插入新的缴费项目，使用存储过程
        /// </summary>
        /// <param name="project"></param>
        public int insertProject(PaymentProject project)
        {
            SqlCommand command = SQLHelper.GetCommandProc("sp_addNewPaymentProject");
            command.Parameters.Add("@PaymentBeginTime", SqlDbType.DateTime);
            command.Parameters.Add("@PaymentEndTime", SqlDbType.DateTime);
            command.Parameters.Add("@OperatorId", SqlDbType.Int);
            command.Parameters.Add("@Target", SqlDbType.NVarChar, 12);
            command.Parameters.Add("@ProjectTypeId", SqlDbType.Int);
            command.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 60);
            command.Parameters.Add("@TotalAmount", SqlDbType.Decimal, 12);
            command.Parameters.Add("@Year", SqlDbType.Int);
            command.Parameters.Add("@PaymentProjectId", SqlDbType.Int);

            
            command.Parameters["@PaymentBeginTime"].Value = project.PaymentBeginTime;
            command.Parameters["@PaymentEndTime"].Value = project.PaymentEndTime;
            command.Parameters["@OperatorId"].Value = project.OperatorId;
            command.Parameters["@Target"].Value = project.Target;
            command.Parameters["@ProjectTypeId"].Value = project.ProjectTypeId;
            command.Parameters["@ProjectName"].Value = project.ProjectName;
            command.Parameters["@TotalAmount"].Value = project.TotalAmount;
            command.Parameters["@Year"].Value = project.Year;

            command.Parameters["@PaymentProjectId"].Direction = ParameterDirection.Output;
            try
            {
                command.Connection.Open();
               
                
            }
            catch
            {
                command.Connection.Close();
            }
            int rowsAffected = command.ExecuteNonQuery();
            int newID = (int)command.Parameters["@PaymentProjectId"].Value;
            

            return newID;

            /*
            string commandText = "Insert into PaymentProject(projectName,beginDate,endDate,totalAmount,target,operatorId,projectTypeId,year) values(@projectName,@beginDate,@endDate,@totalAmount,@target,@operatorId,@projectTypeId,@year)";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("projectName",project.ProjectName),
                new SqlParameter("beginDate", project.PaymentBeginTime),
                new SqlParameter("endDate", project.PaymentEndTime),
                new SqlParameter("totalAmount", project.TotalAmount),
                new SqlParameter("target", project.Target),
                new SqlParameter("operatorId", project.OperatorId),
                new SqlParameter("projectTypeId", project.ProjectTypeId),
                new SqlParameter("year", project.Year)
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);
            */


        }

        public  int selectMaxProjectId()
        {
            int result=0;
            string commandText = string.Format("select Max(projectId) from PaymentProject");
            //string commandText = string.Format("select dbo.f_NextOrderId()");
            result = (int)SQLHelper.ExecuteScalar(commandText);
            return result;
       }

        public DataSet selectPaymentProjectType()
        {
            string commandText = "select * from PaymentType";
            return SQLHelper.ExecuteDataSet(commandText);
        }
      
      
       
        //得到每个学生的缴费项目
        public DataSet getStudentPaymentProjectDS(String stuId)
        { 
             string commandText = "select * from StudentPaymentProject,PaymentProject where PaymentProject.projectId=StudentPaymentProject.projectId and stuId='" + stuId + "' and projectStatus='未缴清' ";
             return SQLHelper.ExecuteDataSet(commandText);
        }

        public void insertStudentProject(PaymentProject project, List<string> studentIds)
        {
              
                for (int i = 0; i < studentIds.Count; i++)
                {

                    String commandText = "Insert into StudentPaymentProject (stuId,projectId,projectStatus,totalPaidAmount) values(@stuId,@projectId,@projectStatus,@totalPaidAmount)";
                    SqlParameter[] paras = new SqlParameter[]
                    {
                         new SqlParameter("projectId",project.PaymentProjectId),
                         new SqlParameter("totalPaidAmount",project.TotalAmount),
                         new SqlParameter("projectStatus", "未付款"),
                         new SqlParameter("stuId",studentIds[i])
                   };
                    SQLHelper.ExecuteNonQuery(commandText, paras);
                }

          }
        public string[] getStudentObjectId(String stuId)
        {
            string[] objectId = new string[2];
            string commandText = "select firstMajor,secondMajor from student where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("stuId", stuId)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                objectId[0] = Convert.ToString(sdReader["firstMajor"]);
                objectId[1] = Convert.ToString(sdReader["secondMajor"]);
            }
            return objectId;
        }

        public List<String> selectStuProjectIds(PaymentProject project, List<string> studentIds)
        {
            List<String> stuProjectIds = new List<String>();
            string commandText = "select stuProjectId from StudentPaymentProject where stuId=@stuId and projectId=@projectId";//批量查询
            for (int i = 0; i < studentIds.Count; i++)
           {
               SqlParameter[] paras = new SqlParameter[]
               {    
                 new SqlParameter("stuId", studentIds[i]),
                 new SqlParameter("projectId", project.PaymentProjectId)
               };
               SqlDataReader objReader = SQLHelper.ExecuteReader(commandText, paras);
               if (objReader.Read())
               {
                  stuProjectIds.Add(Convert.ToString(objReader["stuProjectId"]));
                }
            }
            return stuProjectIds;
        }

        public DataSet getPaymenmtProdectList(string year, string operatorId)
        {
            String commandText = " select * " +
                             "  from PaymentProject,Operator " +
                             "  where year like @year " +
                                " and Operator.operatorId like @operatorId " +
	                            " and Operator.operatorId = PaymentProject.operatorId ";
            SqlParameter[] paras = new SqlParameter[]
            {    
                new SqlParameter("@year", year),
                new SqlParameter("@operatorId", operatorId)
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }
        

    }
}
